	/*
		DDL pro projekt GJA - code storage
		Autor: Martin Valenta
		Datum: 21.4.2013
	*/
	
	/*------------------------------------USER------------------------------------*/
	
	CREATE TABLE [User](
		[IDUser] [int] IDENTITY(1,1) NOT NULL,
		[Email] [varchar](100) NULL,
		[Login] [varchar](100) NOT NULL,
		[Password] [varchar](100) NOT NULL,
		[IsAdmin] [bit] NOT NULL,
	 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
	(
		[IDUser] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_User] UNIQUE NONCLUSTERED 
	(
		[Login] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY] 
	GO

	ALTER TABLE [User] ADD  CONSTRAINT [DF_User_IsAdmin]  DEFAULT ((0)) FOR [IsAdmin]
	GO
	
	
	/*------------------------------------APLIKACE------------------------------------*/
	
	CREATE TABLE [Aplikace](
		[IDAplikace] [int] IDENTITY(1,1) NOT NULL,
		[IDUser] [int] NOT NULL,
		[Nazev] [varchar](100) NOT NULL,
		[Obsah] [varbinary](max) NOT NULL,
		[FileName] [varchar](200) NOT NULL,
		[Popis] [varchar](500) NOT NULL,
		[DatumVytvoreni] [datetime] NOT NULL,
		[Ikona] [varbinary](max) NULL,
		[DatumZmena] [datetime] NULL,
		[PocetStazeni] [int] NULL,
	 CONSTRAINT [PK_Aplikace] PRIMARY KEY CLUSTERED 
	(
		[IDAplikace] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
		
	ALTER TABLE [Aplikace]  WITH CHECK ADD  CONSTRAINT [FK_Aplikace_User] FOREIGN KEY([IDUser])
	REFERENCES [User] ([IDUser])
	ON DELETE CASCADE
	GO
	
	ALTER TABLE [Aplikace] CHECK CONSTRAINT [FK_Aplikace_User]
	GO
	
	ALTER TABLE [Aplikace] ADD  CONSTRAINT [DF_Aplikace_PocetStazeni]  DEFAULT ((0)) FOR [PocetStazeni]
	GO	
	
	/*------------------------------------HODNOCENI------------------------------------*/
	
	CREATE TABLE [Hodnoceni](
		[IDHodnoceni] [int] IDENTITY(1,1) NOT NULL,
		[IDAplikace] [int] NOT NULL,
		[Hodnota] [real] NOT NULL,
		[IPAdresa] [varchar](15) NOT NULL,
	 CONSTRAINT [PK_Hodnoceni] PRIMARY KEY CLUSTERED 
	(
		[IDHodnoceni] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [Hodnoceni_IP] UNIQUE NONCLUSTERED 
	(
		[IPAdresa] ASC,
		[IDAplikace] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	
	ALTER TABLE [Hodnoceni]  WITH CHECK ADD  CONSTRAINT [FK_Hodnoceni_Aplikace] FOREIGN KEY([IDAplikace])
	REFERENCES [Aplikace] ([IDAplikace])
	ON DELETE CASCADE
	GO
	
	ALTER TABLE [Hodnoceni] CHECK CONSTRAINT [FK_Hodnoceni_Aplikace]
	GO
	
	
	/*------------------------------------TAG------------------------------------*/
	
	CREATE TABLE [Tag](
		[IDTag] [int] IDENTITY(1,1) NOT NULL,
		[IDAplikace] [int] NOT NULL,
		[Typ] [smallint] NOT NULL,
		[Hodnota] [varchar](100) NOT NULL,
	 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
	(
		[IDTag] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
	GO
	
	SET ANSI_PADDING OFF
	GO
	
	ALTER TABLE [Tag]  WITH CHECK ADD  CONSTRAINT [FK_Tag_Aplikace] FOREIGN KEY([IDAplikace])
	REFERENCES [Aplikace] ([IDAplikace])
	ON DELETE CASCADE
	GO
	
	ALTER TABLE [Tag] CHECK CONSTRAINT [FK_Tag_Aplikace]
	GO
	
	CREATE NONCLUSTERED INDEX [AplikaceTagIndex] ON [Tag] 
	(
		[IDAplikace] ASC,
		[IDTag] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO
	
	CREATE UNIQUE NONCLUSTERED INDEX [TypHodnotaIndex] ON [Tag] 
	(
		[Typ] ASC,
		[Hodnota] ASC,
		[IDAplikace] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	GO	
